<?php
class ModelSaleCustomer extends Model {
	
	public function addCustomer($data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "customer SET customer_parent = '" . (int)$data['machidinh'] . "', machidinh = '" . (int)$data['magioithieu'] . "', madn = '" . $this->db->escape($data['madn']) . "', price = '" . (int)$data['price'] . "', group_user = '" . (int)$this->user->getGroup() . "', salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "', status = 0, date_added = NOW()");

		$customer_id = $this->db->getLastId();
		$this->db->query("INSERT INTO " . DB_PREFIX . "customer_info SET customer_id = '" . (int)$customer_id . "', name = '" . $this->db->escape($data['name']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', born = '" . $this->db->escape($data['born']) . "', address = '" . $this->db->escape($data['address']) . "'");
		
		if($data['price'] == $this->config->get('config_price')){ 
			
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET `status` = 1 WHERE customer_id = '" . (int)$customer_id . "'");
		}
		if($this->user->getGroup() == 1){ 
			
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET `status` = 1 WHERE customer_id = '" . (int)$customer_id . "'");
		}
		return $customer_id;
	}

	public function editCustomer($customer_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer_info SET name = '" . $this->db->escape($data['name']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', born = '" . $this->db->escape($data['born']) . "', address = '" . $this->db->escape($data['address']) . "' WHERE customer_id = '" . (int)$customer_id . "'");

		if ($data['password']) {
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET salt = '" . $this->db->escape($salt = substr(md5(uniqid(rand(), true)), 0, 9)) . "', password = '" . $this->db->escape(sha1($salt . sha1($salt . sha1($data['password'])))) . "' WHERE customer_id = '" . (int)$customer_id . "'");
		}
	}

	public function editToken($customer_id, $token) {
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET token = '" . $this->db->escape($token) . "' WHERE customer_id = '" . (int)$customer_id . "'");
	}

	public function deleteCustomer($customer_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . (int)$customer_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . (int)$customer_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "customer_ip WHERE customer_id = '" . (int)$customer_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "address WHERE customer_id = '" . (int)$customer_id . "'");
	}

	public function getCustomer($customer_id) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer c, " . DB_PREFIX . "customer_info ci WHERE c.customer_id = " . (int)$customer_id . " AND ci.customer_id = " . (int)$customer_id . $this->session->data['group_db']);
		
		return $query->row;
	}
	
    public function getCustomerForHH($customer_id) {
        $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer c, " . DB_PREFIX . "customer_info ci WHERE c.customer_id = " . (int)$customer_id . " AND ci.customer_id = " . (int)$customer_id);

        return $query->row;
    }

    public function getCustomerByEmail($email) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer_info WHERE LCASE(email) = '" . $this->db->escape(utf8_strtolower($email)) . $this->session->data['group_db'] . "'");

		return $query->row;
	}
	//Get customer by maDN
	public function getCustomerByMaDN($madn) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer c WHERE c.madn = '" . $this->db->escape(utf8_strtolower($madn))."'");
		return $query->row;
	}

	public function getCustomerByIdAndLevel($customer_id,$level) {
		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer c WHERE c.customer_parent = '" . (int)$customer_id."'AND c.level1 ='".(int)$level."'");
		return $query->rows;
	}
	
	public function getCustomers($data = array()) {
		if(isset($data['type']) && $data['type']){
			$sql = "SELECT * FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 ";
		} else {
			$sql = "SELECT * FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];
		}
		$implode = array();

		if (!empty($data['filter_madn'])) {
			$implode[] = "c.madn LIKE '" . $this->db->escape($data['filter_madn']) . "%'";
		}

		if (!empty($data['filter_parent'])) {
			$implode[] = "c.customer_parent = " . $this->db->escape($data['filter_parent']) . "";
		}

		if (!empty($data['filter_level'])) {
			$implode[] = "c.level1 = " . $this->db->escape($data['filter_level']) . "";
		}

		if (!empty($data['filter_email'])) {
			$implode[] = "ci.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_customer'])) {
			$implode[] = "c.madn LIKE '" . $this->db->escape($data['filter_customer']) . "%'";
			$implode[] = "c.status = 1";
		}

		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
		}	
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(c.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(c.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if (!empty($data['filter_date_added'])) {
			$implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}

		$sort_data = array(
			'c.madn',
			'c.customer_parent',
			'ci.name',
			'ci.email',
			'customer_group',
			'c.status',
			'c.approved',
			'c.ip',
			'c.date_added'
		);	
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];	
		} else {
			$sql .= " ORDER BY ci.customer_id";	
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " DESC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}		

		$query = $this->db->query($sql);
		return $query->rows;	
	}
    public function getCustomersForTransactionLevel($data = array()) {

         $sql = "SELECT * FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_parent = '" . (int)$data['customer_parent'] . "' AND c.status = 1 AND lock_flag = 0";

        $query = $this->db->query($sql);
        return $query->rows;
    }

    public function getCustomersForScan($data = array()) {
        $sql = "SELECT c.*, ci.* FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) LEFT JOIN " . DB_PREFIX . "customer_history ch ON (c.customer_id = ch.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

        $implode = array();

        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
        }
        if (!empty($data['filter_date_start']) && !empty($data['filter_date_end'])) {
            $implode[] = "((DATE(c.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "' AND DATE(c.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "') OR (DATE(ch.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "' AND DATE(ch.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'))";
        }
        if ($implode) {
            $sql .= " AND " . implode(" AND ", $implode);
        }
        $sql .= " ORDER BY c.date_added";
        $sql .= " ASC";
        // print_R($sql);exit;
        $query = $this->db->query($sql);
        return $query->rows;
    }

	public function getCustomersByParent($customer_id) {
		$sql = "SELECT *, ci.name AS name FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_parent = ".(int)$customer_id . $this->session->data['group_db'];

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];	
		} else {
			$sql .= " ORDER BY ci.name";	
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
		$query = $this->db->query($sql);

		return $query->rows;	
	}
	
	//Get Hoa Hong Truc Tiep
	public function getHHTrucTiep($data = array()) {
		$sql = "SELECT c.customer_id AS customer_id, c.madn AS madn, ci.name AS name, ci.email AS email, hhtt.price AS price, hhtt.customer AS customer_parent, hhtt.date_added AS date_added, hhtt.date_edit AS date_edit, hhtt.status AS status FROM " . DB_PREFIX . "customer c RIGHT JOIN " . DB_PREFIX . "hoahong_tructiep hhtt ON (c.customer_id = hhtt.customer_id) LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['customer_id'])) {
			$implode[] = " hhtt.customer = '" . (int)$data['customer_id'] . "'";
		}
		
		if (!empty($data['status']) && $data['status'] == 'true') {
			$implode[] = " hhtt.status = 0";
		}
		
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(hhtt.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(hhtt.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$sql .= " ORDER BY hhtt.date_added DESC";	
		if (empty($data['customer_id'])) {
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}			

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	

				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}		
		}
		$query = $this->db->query($sql);

		return $query->rows;
	}
	//transactions Hoa Hong Truc Tiep
	public function transactionsHHTrucTiep($data = array()) {
		$sql = "UPDATE " . DB_PREFIX . "hoahong_tructiep SET status = 1, date_edit = NOW() WHERE status = 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['customer_id'])) {
			$implode[] = " customer = '" . (int)$data['customer_id'] . "'";
		}
		
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);

	}
	//Get Hoa Hong Gian Tiep
	public function getHHGianTiep($data = array()) {
		$sql = "SELECT c.customer_id AS customer_id, c.madn AS madn, ci.name AS name, ci.email AS email, hhgt.price AS price, hhgt.customer AS customer_parent, hhgt.date_added AS date_added, hhgt.date_edit AS date_edit, hhgt.status AS status FROM " . DB_PREFIX . "customer c RIGHT JOIN " . DB_PREFIX . "hoahong_giantiep hhgt ON (c.customer_id = hhgt.customer_id) LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();
		
		if (!empty($data['customer_id'])) {
			$implode[] = " hhgt.customer = '" . (int)$data['customer_id'] . "'";
		}
		
		if (!empty($data['status']) && $data['status'] == 'true') {
			$implode[] = " hhgt.status = 0";
		}
		
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(hhgt.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(hhgt.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$sql .= " ORDER BY hhgt.date_added DESC";
		if (empty($data['customer_id'])) {
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}			

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	

				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}		
		}
		$query = $this->db->query($sql);

		return $query->rows;	
	}

	//transactions Hoa Hong Gian Tiep
	public function transactionsHHGianTiep($data = array()) {
		$sql = "UPDATE " . DB_PREFIX . "hoahong_giantiep SET status = 1, date_edit = NOW() WHERE status = 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['customer_id'])) {
			$implode[] = " customer = '" . (int)$data['customer_id'] . "'";
		}
		
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		
		$query = $this->db->query($sql);

	}
	
	//Add transactions history
	public function addTransactionsCustomer($customer_id, $total, $comment){
		$this->db->query("INSERT INTO " . DB_PREFIX . "history_transaction SET customer_id = '" . (int)$customer_id . "', 	price = '" . (int)$total . "', comment = '" . $this->db->escape($comment) . "', date_added = NOW()");

	}

    //Add transactions history level
    public function addTransactionsCustomerForLevel($customer_id, $comment){
        $this->db->query("INSERT INTO " . DB_PREFIX . "transaction_level SET customer_id = '" . (int)$customer_id . "', comment = '" . $this->db->escape($comment) . "', date_added = NOW()");

    }
	//Get Hoa Hong Transaction
	public function getTransactionsLevel($data = array()) {
		$sql = "SELECT c.madn AS madn, ci.name AS name, ci.email AS email, ht.date_added AS date_added, ht.comment AS comment FROM " . DB_PREFIX . "customer c RIGHT JOIN " . DB_PREFIX . "transaction_level ht ON (c.customer_id = ht.customer_id) LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['customer_id'])) {
			$implode[] = " ht.customer_id = '" . (int)$data['customer_id'] . "'";
		}

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$sql .= " ORDER BY ht.date_added DESC";
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}		

		$query = $this->db->query($sql);

		return $query->rows;	
	}
	public function getTransactions($data = array()) {
		$sql = "SELECT c.madn AS madn, ci.name AS name, ci.email AS email, ht.price AS price, ht.date_added AS date_added, ht.comment AS comment FROM " . DB_PREFIX . "customer c RIGHT JOIN " . DB_PREFIX . "history_transaction ht ON (c.customer_id = ht.customer_id) LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['customer_id'])) {
			$implode[] = " ht.customer_id = '" . (int)$data['customer_id'] . "'";
		}

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$sql .= " ORDER BY ht.date_added DESC";
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}			

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}		

		$query = $this->db->query($sql);

		return $query->rows;	
	}//Get Hoa Hong Transaction
	public function getTransactionByCustomer($customer_id) {
		$sql = "SELECT ht.* FROM " . DB_PREFIX . "history_transaction ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE c.customer_id = " .(int)$customer_id . $this->session->data['group_db'];

		$query = $this->db->query($sql);

		return $query->rows;	
	}
	//Function get Total Sales
	public function getTotalPrice1($data = array()) {
		$sql = "SELECT SUM(c.price) AS total FROM " . DB_PREFIX . "customer c WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(c.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(c.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		
		$query = $this->db->query($sql);

		return $query->row['total'];	
	}
	
	//Function get Total Sales 2
	public function getTotalPrice2($data = array()) {
		$sql = "SELECT SUM(ch.price) AS total FROM " . DB_PREFIX . "customer_history ch JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ch.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ch.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ch.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);

		return $query->row['total'];	
	}

	//Function get Total Sales HH Truc Tiep
	public function getTotalHHPriceTrucTiep($data = array()) {
		$sql = "SELECT SUM(ht.price) AS total FROM " . DB_PREFIX . "hoahong_tructiep ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);

		return $query->row['total'];	
	}
	
	public function getTotalHHTrucTiep($data = array()) {
		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "hoahong_tructiep ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);

		return $query->row['total'];	
	}

	public function getTotalHHGianTiep($data = array()) {
		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "hoahong_giantiep hg JOIN " . DB_PREFIX . "customer c ON (c.customer_id = hg.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(hg.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(hg.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);

		return $query->row['total'];	
	}

	//Function get Total Sales HH Gian Tiep
	public function getTotalHHPriceGianTiep($data = array()) {
		$sql = "SELECT SUM(hg.price) AS total FROM " . DB_PREFIX . "hoahong_giantiep hg JOIN " . DB_PREFIX . "customer c ON (c.customer_id = hg.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();
		
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(hg.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(hg.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);

		return $query->row['total'];	
	}


	public function getTotalCustomers($data = array()) {
		$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer c LEFT JOIN " . DB_PREFIX . "customer_info ci ON (c.customer_id = ci.customer_id) WHERE c.customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();
			if (!empty($data['filter_madn'])) {
			$implode[] = "c.madn LIKE '" . $this->db->escape($data['filter_madn']) . "%'";
		}

		if (!empty($data['filter_parent'])) {
			$implode[] = "c.customer_parent = " . $this->db->escape($data['filter_parent']) . "";
		}
		if (!empty($data['filter_name'])) {
			$implode[] = "ci.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
		}

		if (!empty($data['filter_email'])) {
			$implode[] = "ci.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
			$implode[] = "status = '" . (int)$data['filter_status'] . "'";
		}			

		if (!empty($data['filter_date_added'])) {
			$implode[] = "DATE(date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}
			
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(c.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(c.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= 'AND ' . implode(" AND ", $implode);
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getTotalTransactions($data = array()) {
		$sql = "SELECT COUNT(*) AS total  FROM " . DB_PREFIX . "history_transaction ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE c.customer_id != 0" . $this->session->data['group_db'];
		$implode = array();
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= ' AND ' . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);
		return $query->row['total'];
	}
	public function getTotalTransactionsLevel($data = array()) {
		$sql = "SELECT COUNT(*) AS total  FROM " . DB_PREFIX . "transaction_level ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE c.customer_id != 0" . $this->session->data['group_db'];
		$implode = array();
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= ' AND ' . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);
		return $query->row['total'];
	}
	
	 public function addHoaHongTrucTiep($data){
	 
		$this->db->query("INSERT INTO `" . DB_PREFIX . "hoahong_tructiep`
		SET customer_id='".(int)$data['customer_id']."',
		    customer='".(int)$data['customer_parent']."',
		    price='".(int)$data['price']."',
		    date_added = NOW()
		    ");
	 }
    public function  addHoaHongGianTiep($customer_id,$customer_parent,$price){
        $this->db->query("INSERT INTO `" .DB_PREFIX. "hoahong_giantiep`
        SET customer_id='".(int)$customer_id."',
            customer='".(int)$customer_parent."',
            price='".$price."', date_added = NOW()
        ");
    }
    public function updateIndexValueCustomer($customer_id,$value,$type){
		if($type){
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET index_value2='".$value."'
                                    WHERE customer_id = '".(int)$customer_id."'
			");
		}else{
			$this->db->query("UPDATE " . DB_PREFIX . "customer SET index_value1='".$value."'
                                    WHERE customer_id = '".(int)$customer_id."'
			");
		}
        

    }
    public function updateLevelCustomer($customer_id,$level,$type){
		if($type){
			$this->db->query("UPDATE ". DB_PREFIX ."customer SET level2='".$level."',
										index_value2=0
										WHERE customer_id = '".(int)$customer_id."'
			");
		}else{
			$this->db->query("UPDATE ". DB_PREFIX ."customer SET level1='".$level."',
										index_value1=0
										WHERE customer_id = '".(int)$customer_id."'
			");
		}
    }

    public function updateLevelNhanhCustomer($customer_id,$level){
        $this->db->query("UPDATE ". DB_PREFIX . "customer SET level2='".$level."',
                                    index_value2=0
                                    WHERE customer_id = '".(int)$customer_id."'
        ");
    }
    public function updateIndexValueNhanhCustomer($customer_id,$value){
        $this->db->query("UPDATE ".DB_PREFIX."customer set index_value2='".$value."'
                                    WHERE customer_id='".(int)$customer_id."'
         ");
    }
    public function updateLevelChilden($customer_id,$level_childen){
        $this->db->query("UPDATE ".DB_PREFIX."customer set level_childen='".$level_childen."'
                                    WHERE customer_id='".(int)$customer_id."'
         ");
    }
	public function updateLevelChildenNhanh($customer_id,$level_childen_nhanh){
        $this->db->query("UPDATE ".DB_PREFIX."customer set level_childen_nhanh='".$level_childen_nhanh."'
                                    WHERE customer_id='".(int)$customer_id."'
         ");
    }
	//get Price for update price
	
	public function updatePrice($customer_id,$data) {
		$this->db->query("INSERT INTO `" . DB_PREFIX . "customer_history` SET `customer_id` = '" . (int)$customer_id . "', `price` = '" . (int)$data['price_update'] . "', date_added = NOW()");
		$this->db->query("UPDATE " . DB_PREFIX . "customer SET status = 1 WHERE customer_id = '" . (int)$customer_id . "'");
	}	

	public function getHistoriesPrice2($customer_id) {
		$query = $this->db->query("SELECT ch.* FROM " . DB_PREFIX . "customer_history ch JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ch.customer_id) WHERE c.customer_id = " . (int)$customer_id . $this->session->data['group_db']);
		return $query->row;
	}	
	
	//functions get info customer
	
	
	public function getAllhhBaotro($manhan,$data = array()) {
		//$query = $this->db->query("SELECT ht.* FROM " . DB_PREFIX . "hoahong_tructiep ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE ht.customer = '" . $this->db->escape($manhan) . $this->session->data['group_db'] . "' ORDER BY c.customer_id DESC");
		$sql = "SELECT ht.* FROM " . DB_PREFIX . "hoahong_tructiep ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE ht.customer = '" . $this->db->escape($manhan) . $this->session->data['group_db'] . "'";
		$implode = array();
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(ht.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(ht.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= ' AND ' . implode(" AND ", $implode);
		}
		$sql .= ' ORDER BY c.customer_id DESC';
		$query = $this->db->query($sql);
		return $query->rows;
	}
	public function getTotalHoahongTructiep($manhan) {
		$query = $this->db->query("SELECT sum(ht.price) as total FROM " . DB_PREFIX . "hoahong_tructiep ht JOIN " . DB_PREFIX . "customer c ON (c.customer_id = ht.customer_id) WHERE ht.customer = '" . $this->db->escape($manhan) . $this->session->data['group_db'] . "' ORDER BY c.customer_id DESC");
	
		return $query->row;
	}
	
	public function getTotalHoahongGiantiep($manhan) {
		$query = $this->db->query("SELECT sum(hg.price) as total FROM " . DB_PREFIX . "hoahong_giantiep hg JOIN " . DB_PREFIX . "customer c ON (c.customer_id = hg.customer_id) WHERE hg.customer = '" . $this->db->escape($manhan) . $this->session->data['group_db'] . "' ORDER BY c.customer_id DESC");
	
		return $query->row;
	}
	
	public function gethhTuvan($manhan,$data=array()) {
		$sql = "SELECT hg.* FROM " . DB_PREFIX . "hoahong_giantiep hg JOIN " . DB_PREFIX . "customer c ON (c.customer_id = hg.customer_id) WHERE hg.customer = '" . $this->db->escape($manhan) . $this->session->data['group_db'] . "'";
		$implode = array();
		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(hg.date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(hg.date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		
		if ($implode) {
			$sql .= ' AND ' . implode(" AND ", $implode);
		}
		$sql .= ' ORDER BY c.customer_id DESC';
		$query = $this->db->query($sql);
		return $query->rows;
	}
	

	public function checkExistCustomer($data = array()) {
		$sql = "SELECT DISTINCT customer_id FROM " . DB_PREFIX . "hoahong_tructiep WHERE customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);
		$array_temp1 = 	 $query->rows;
		
		$sql = "SELECT DISTINCT customer_id FROM " . DB_PREFIX . "hoahong_giantiep WHERE customer_id != 0 " . $this->session->data['group_db'];

		$implode = array();

		if (!empty($data['filter_date_start'])) {
			$implode[] = " DATE(date_added) >= '" . $this->db->escape($data['filter_date_start']) . "'";
		}

		if (!empty($data['filter_date_end'])) {
			$implode[] = " DATE(date_added) <= '" . $this->db->escape($data['filter_date_end']) . "'";
		}
		if ($implode) {
			$sql .= " AND " . implode(" AND ", $implode);
		}
		$query = $this->db->query($sql);
		$array_temp2 = 	 $query->rows;
		$temps1 = array();
		$temps2 = array();
		if($array_temp1){
			foreach($array_temp1 as $value){
				$temps1[] = $value['customer_id'];
			}
		}
		if($array_temp2){
			foreach($array_temp2 as $value){
				$temps2[] = $value['customer_id'];
			}
		}
		if($temps1){
			if($temps2){
				foreach($temps2 as $value){
					if (!in_array($value, $temps1)) {
						$temps1[] = $value;
					}
				}
			}
			return $temps1;
		} else{return $temps2;}
		
	}
	
}
?>